﻿using Dapper;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using zzh_the_Visual_Studio.Models;

namespace zzh_the_Visual_Studio.DAL
{
    public class DAL_Manage
    {
        private MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);

        //主页，显示管理员账号
        public List<manage> getManage()
        {
            var list = mySqlConnection.Query<manage>("SELECT m_number FROM manage").ToList();
            return list;
        }
        //查询学生的详细信息，姓名，学号，电话，身份证，学年，班级名称，专业名称，二级学院
        public List<manage_students> detailStudents()
        {
            var list = mySqlConnection.Query<manage_students>("SELECT * FROM manage_students").ToList();
            return list;
        }
        //学生页面翻页,page代表前端返回的页数
        public List<manage_students> limitStudents(int page)
        {
            var list = mySqlConnection.Query<manage_students>("SELECT * FROM `manage_students` LIMIT @page,10", new { page }).ToList();
            return list;
        }
        //学生模糊查询
        public List<manage_students> selectStudents(string s)
        {
            var list = mySqlConnection.Query<manage_students>($"SELECT * FROM `manage_students` WHERE CONCAT(IFNULL(`s_name`,''),IFNULL(`s_sex`,''),IFNULL(`s_phone`,''),IFNULL(`s_idcard`,''),IFNULL(`s_year`,''),IFNULL(`s_number`,''),IFNULL(`class_name`,''),IFNULL(`major_name`,''),IFNULL(`college_name`,'')) LIKE '%{s}%'").ToList();
            return list;
        }
        //查询教师详细信息，姓名，员工号，性别，电话，身份证号，二级学院，学科
        public List<manage_teachers> detailTeachers()
        {
            var list = mySqlConnection.Query<manage_teachers>("SELECT * FROM manage_teachers").ToList();
            return list;
        }
        //教师页面翻页,page代表前端返回的页数
        public List<manage_teachers> limitTeachers(int page)
        {
            var list = mySqlConnection.Query<manage_teachers>("SELECT * FROM `manage_teachers` LIMIT @page,10", new { page }).ToList();
            return list;
        }
        //教师模糊查询
        public List<manage_teachers> selectTeachers(string s)
        {
            var list = mySqlConnection.Query<manage_teachers>($"SELECT * FROM `manage_teachers` WHERE CONCAT(IFNULL(`teacher_name`,''),IFNULL(`teacher_sex`,''),IFNULL(`t_phone`,''),IFNULL(`t_idcard`,''),IFNULL(`t_number`,''),IFNULL(`t_number`,''),IFNULL(`college_name`,'')) LIKE '%{s}%'").ToList();
            return list;
        }
        //添加学生信息
        public int createStudents(students s)
        {
            var sql = "INSERT INTO students (s_id,s_name,s_sex,s_phone,s_idcard,s_year,class_id,major_id,college_id,s_number,`password`) VALUES (@s_id,@s_name,@s_sex,@s_phone,@s_idcard,@s_year,@class_id,@major_id,@college_id,@s_number,@`password`)";
            var affectedRows = mySqlConnection.Execute(sql, s);
            return affectedRows;
        }
        //删除学生信息
        public int deleteStudents(int id)
        {
            var sql = "delete from students where id=@id";
            var affectedRows = mySqlConnection.Execute(sql, new { id });
            return affectedRows;
        }
        //修改学生信息，修改姓名、性别、电话号码、学年制
        public int changeStudents(students s)
        {
            var sql = "UPDATE students SET s_name=@s_name,s_sex=@s_sex,s_phone=@s_phone,s_year=@s_year WHERE s_number=@s_number";
            var affectedRows = mySqlConnection.Execute(sql, s);
            return affectedRows;
        }
        //新增教师信息
        public int createTeachers(teachers t)
        {
            var sql = "INSERT INTO teachers (teacher_name,teacher_id,teacher_sex,t_phone,t_idcard,college_id,t_number,`password`) VALUES (@teacher_name,@teacher_id,@teacher_sex,@t_phone,@t_idcard,@college_id,@t_number,@`password`)";
            var affectedRows = mySqlConnection.Execute(sql, t);
            return affectedRows;
        }
        //删除教师信息
        public int deleteTeachers(int id)
        {
            var sql = "delete from teachers where id=@id";
            var affectedRows = mySqlConnection.Execute(sql, new { id });
            return affectedRows;
        }
        //修改教师信息，修改老师姓名、性别、联系方式、二级学院
        public int changeTeachers(teachers t)
        {
            var sql = "UPDATE teachers SET teacher_name=@teacher_name,teacher_sex=@teacher_sex,t_phone=@t_phone,college_id=@college_id WHERE t_number=@t_number";
            var affectedRows = mySqlConnection.Execute(sql, t);
            return affectedRows;
        }
        //查询学生成绩
        public List<students_scores> getScores()
        {
            var list = mySqlConnection.Query<students_scores>("SELECT * FROM students_scores").ToList();
            return list;
        }
        //成绩模糊查询
        public List<students_scores> selectScores(string s)
        {
            var list = mySqlConnection.Query<students_scores>($"SELECT * FROM `students_scores` WHERE CONCAT(IFNULL(`s_number`,''),IFNULL(`s_name`,''),IFNULL(`subject`,''),IFNULL(`score`,''),IFNULL(`teacher_name`,'')) LIKE '%{s}%'").ToList();
            return list;
        }
        //修改成绩
        public int changeScores(scores s)
        {
            var sql = "UPDATE scores SET score=@score WHERE id=@id";
            var affectedRows = mySqlConnection.Execute(sql, s);
            return affectedRows;
        }
    }
}